<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 8.3.&nbsp; Backing Up and Restoring Data</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-2.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-4.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-8-SECT-3"></a>
<h3 id="631368-948" class="docSection1Title">8.3. Backing Up and Restoring Data</h3>
<a name="IDX-CHP-8-0382"></a> 
<a name="IDX-CHP-8-0383"></a> 
<a name="IDX-CHP-8-0384"></a> 
<a name="IDX-CHP-8-0385"></a> 
<a name="IDX-CHP-8-0386"></a> 
<a name="IDX-CHP-8-0387"></a> 

<p class="docText">Even the best maintained databases occasionally develop problems. Hardware failures, in particular, can really throw a monkey wrench into your web pages. Now that you're using a database, just backing up the files (HTML, PHP, and images) on your web server isn't enough. There's nothing worse than informing your web users that they have to reenter information, such as their accounts, or have to recreate your catalog items. Having a complete backup can make the difference between an hour of down time and having to recreate the wheel. There are a couple of tactics that we'll discuss for backing up your database data.</p>
<a name="learnphpmysql-CHP-8-SECT-3.1"></a>
<h4 id="title-IDAQV5LM" class="docSection2Title">8.3.1. Copying Database Files</h4>
<p class="docText">You can also do a simple file backup of your MySQL database's datafiles, in the same way that you can back up your HTML and PHP files. If you can back up files, you can back up the MySQL database files.</p>
<p class="docText">We don't recommend this tactic for moving a database from one machine to another server, since different versions of MySQL may expect these files to be in a different format. MySQL stores its datafiles in a special data directory that is usually located in <span class="docEmphasis">C:\Program Files\MySQL\MySQL Server 4.1\data\[database_name]</span> on Windows and in <span class="docEmphasis">/var/lib/mysql</span> on Unix variants such as Linux and Mac OS X.</p>
<p class="docText">To fully back up and restore a MySQL database using your current datafiles, all the files must be replaced in the same directory from which they were backed up. Then, the database must be restarted.</p>

<a name="learnphpmysql-CHP-8-SECT-3.2"></a>
<h4 id="title-IDACW5LM" class="docSection2Title">8.3.2. The mysqldump Command</h4>
<a name="IDX-CHP-8-0388"></a> 

<p class="docText">It's better to use the MySQL command-line tool for making complete database backups.<a name="IDX-CHP-8-0389"></a> 
<a name="IDX-CHP-8-0390"></a> 
 The same tools you'll use to back up and restore can also be used to change platforms or move your database from one server to another; <tt>mysqldump</tt> creates a text file containing the SQL statements required to rebuild the database objects and insert the data. The <tt>mysqldump</tt> command is accessible from the command line and takes parameters for backing up a single table, a single database, or everything. The command's syntax is:</p>
<pre>
mysqldump -u <tt><i>user</I></tt> -p <tt><i>objects_to_backup</i></tt>
</pre><BR>

<p class="docText">The default mode for <tt>mysqldump</tt> is to export to backup and then to standard output, which is usually the screen.</P>
<a name="learnphpmysql-CHP-8-SECT-3.2.1"></a>
<h5 id="title-IDAJX5LM" class="docSection3Title">8.3.2.1. Backing up</H5>
<p class="docText">We're going to show you the commands to back up a database called <tt>test</tt> from the shell prompt.</p>
<pre>
mysqldump -u root -p test &gt; my_backup.sql
</pre><br>

<p class="docText">This tells <tt>mysqldump</tt> to log into the database as the <tt>root</tt> user with a password of <tt>barney</tt>, and to back up the <tt>test</tt> database. The output of the command is saved to a file called <span class="docEmphasis">my_backup.sql</span> with the help of the redirect character also known as the greater-than symbol (<tt>&gt;</tt>).</p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-8-EX-1">Example 8-1</a> shows the first portion of the output <tt>mysqldump</tt> creates.</P>
<a name="learnphpmysql-CHP-8-EX-1"></a><h5 id="title-IDARY5LM" class="docExampleTitle">Example 8-1. The contents of the my_backup.sql file</h5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
-- MySQL dump 10.9
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.1.11-Debian_4-log
--
-- Table structure for table `authors`
--
DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `author_id` int(11) NOT NULL auto_increment,
`title_id` int(11) NOT NULL default '0',
  `author` varchar(125) default NULL,
  PRIMARY KEY  (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `authors`
--
/*!40000 ALTER TABLE `authors` DISABLE KEYS */;
LOCK TABLES `authors` WRITE;
INSERT INTO `authors` VALUES (1,1,'Ellen Siever'),(2,1,'Aaron Weber'),(3,2,'Arno
ld Robbins'),(4,2,'Nelson  Beebe');
UNLOCK TABLES;
/*!40000 ALTER TABLE `authors` ENABLE KEYS */;
</pre><BR>

</td></tr></table></p>
<p class="docText">The two major sections in <a class="docLink" href="#learnphpmysql-CHP-8-EX-1">Example 8-1</a> are creating the <tt>authors</tt> table and populating the data for the table.</p>
<p class="docText">To back up only a single table from a database, simply add the table name after the database name. For example, the command below illustrates how to back up only the <tt>authors</tt> table:</P>
<pre>
$ mysqldump -u root -p test <B>authors</b> &gt; authors.sql
</pre><br>

<p class="docText">Most of the time, you'll just want to back up everything in the database. To do this, use the <tt>--all-databases</tt> command-line switch. The resulting database backup file will contain the commands necessary to create the databases and users, making a complete database restore a snap. Here's how to use this parameter:</p>
<pre>
$ mysqldump -u root -p --<b>all-databases</b> &gt; my_backup.sql
</pre><br>

<p class="docText">To create an empty copy of your databasejust the structurefor testing, use the <tt>--no-data</tt> switch:</P>
<pre>
$ mysqldump -u root -p --<b>no-data</b> test &gt; structure.sql
</pre><BR>

<p class="docText">You can also do the opposite and just back up the data with the <tt>--no-create-info</tt> switch like this:</p>
<pre>
$ mysqldump -u root -p --<B>no-create-info</b> test &gt; data.sql
</pre><BR>

<p class="docText">Of course, having a backup of your database doesn't do you much good if you don't know how to restore the database from it.</P>

<a name="learnphpmysql-CHP-8-SECT-3.2.2"></a>
<h5 id="title-IDAP05LM" class="docSection3Title">8.3.2.2. Restoring a MySQL backup</h5>
<p class="docText">The good news is it's not difficult to recreate your database from a <tt>mysqldump</tt> file. As you saw in <a class="docLink" href="#learnphpmysql-CHP-8-EX-1">Example 8-1</a>, the contents of the backup file are simply SQL statements and can therefore be processed by the <tt>mysql</tt> command-line client to restore the backed-up data.</p>
<p class="docText">If you did a backup of your database using <tt>mysqldump --all-databases</tt> to a file called <span class="docEmphasis">my_backup.sql</span>, you could restore your database like this:</p>
<pre>
mysql -u root -p &lt; my_backup.sql
</pre><br>

<p class="docText">If you did a selective backup of only one database, it's a bit more complex. To restore that type of backup file, use the <tt>-D</tt> command-line switch:</p>
<pre>
mysql -u root -p <b>-D test</b> &lt; my_backup.sql
</pre><br>

<p class="docText">Now that you know how to restore default dump files, we can move on to some other applications regarding exporting and importing data.</p>

<a name="learnphpmysql-CHP-8-SECT-3.2.3"></a>
<h5 id="title-IDAX15LM" class="docSection3Title">8.3.2.3. Working with other formats</h5>
<p class="docText">Although working with SQL-based files is convenient, there may be times when you want to save your data in other formats. For example, a common method of representing a list of data is in <span class="docEmphasis">CSV</span> (comma-separated values) format. The <tt>mysqldump</tt> command supports this format. All you need to do is specify the <tt>--no-create-info, --tab</tt>, and <tt>--fields-terminated-by</tt> arguments like this:</p>
<pre>
mysqldump -u root -p --no-create-info --tab=/home/jon --fields-terminated-by=',' test
</pre><br>

<p class="docText">This tells <tt>mysqldump</tt> to generate separate files for each table in the <tt>test</tt> database. They'll all be placed in the directory <span class="docEmphasis">/home/jon</span>. Each file's name will be the name of the table that is being exported. Each file contains the records in the respective table separated by the comma character (,) that was specified on the command line.</p>

<a name="learnphpmysql-CHP-8-SECT-3.2.4"></a>
<H5 id="title-IDAU25LM" class="docSection3Title">8.3.2.4. The mysqlimport command</h5>
<p class="docText">When you're setting up your database, you may need to bring in data from another database or a spreadsheet in CSV format. For example, if you're offering books for sale, you may bring in the existing catalog of books. To import the data displayed in <a class="docLink" href="#learnphpmysql-CHP-8-EX-2">Example 8-2</a>, use the <tt>mysqlimport</tt> command.</p>
<a name="learnphpmysql-CHP-8-EX-2"></a><H5 id="title-IDAE35LM" class="docExampleTitle">Example 8-2. Book titles in CSV format</H5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
1,Linux in a Nutshell,476
2,Classic Shell Scripting,256
</pre><br>

</td></TR></table></p>
<p class="docText">Like this:</p>
<pre>
mysqlimport -u root -p --fields-terminated-by=',' test /home/jon/books.txt
</pre><BR>

<p class="docText">The main portion of the filename (not including the path or the file extension) determines the name of the table. In the example above, the table name is <tt>books</tt>. The table must already exist or an error displays. Another useful keyword is <tt>ENCLOSED BY</tt> <tt><i>char</i></tt><tt>;</tt>, which allows you to specify characters, such as double quotes (<tt>"</tt>) that enclose each field in the file. This is useful for avoiding the problem with a book title like <span class="docEmphasis">Classic Shell Scripting</span>, Second Edition, which would otherwise cause <tt>mysqlimport</tt> to process the "Second Edition" portion of the title as the start of the next field.</P>

<a name="learnphpmysql-CHP-8-SECT-3.2.5"></a>
<h5 id="title-IDAC45LM" class="docSection3Title">8.3.2.5. Backup best practices</h5>
<a name="IDX-CHP-8-0391"></a> 

<p class="docText">Depending on how critical your data is and how often it changes, you can determine how often to back it up. As a rule, weekly, bi-weekly, and monthly are the most common schedules. If your business is completely dependent on your database, you should do a weekly backup schedule, if not backing up daily. Also, keeping a copy of the data in a separate location is a good idea in the event of large scale disasters, such as a fire. A client of ours keeps bi-monthly backups in a fire safe at the office, whereas another client sends the data to a backup service. A backup service can use physical hard drives, tapes, or CDs, or can log into your server and perform the backup electronically.</p>



</TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-2.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-8-SECT-4.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</html>
